import java.sql.*;
import  java.util.*;

class Database {
    Connection con = null;
    //连接数据库
    public Connection connection() {
        Scanner sc = new Scanner(System.in);
        System.out.print("请输入数据库地址，本地数据库地址为localhost：");
        String url = sc.next();
        System.out.print("请输入数据库名称：");
        String urlName = sc.next();
        System.out.print("请输入用户名：");
        String user = sc.next();
        System.out.print("请输入密码：");
        String password = sc.next();
        try {
            Class.forName("com.mysql.jdbc.Driver");
            System.out.println("驱动加载成功！");
            con = DriverManager.getConnection("jdbc:mysql://" + url + "/" + urlName + "?useSSL=false", user, password);
            System.out.println("数据库连接成功！");
        } catch (SQLException e) {
            e.printStackTrace();
            System.out.println("数据库连接错误！");
        } catch (ClassNotFoundException e) {
            System.out.println("驱动加载错误！");
            e.printStackTrace();
        }
        return con;
    }

    //增加数据
    public void add(Connection con) {
        Scanner sc = new Scanner(System.in);
        System.out.println("请依次输入ID、姓名、性别、年龄（以空格分隔数据）");
        int id = sc.nextInt();
        String name = sc.next();
        String sex = sc.next();
        int age = sc.nextInt();
        String sql = "insert into q1 values (?,?,?,?)";
        try {
            PreparedStatement stmt = con.prepareStatement(sql);
            stmt.setInt(1, id);
            stmt.setString(2, name);
            stmt.setString(3, sex);
            stmt.setInt(4, age);
            stmt.executeUpdate();
            System.out.println("添加成功！");
        } catch (SQLException e) {
            e.printStackTrace();
        }

    }

    //删除数据
    public void delete(Connection con) {
        Scanner sc = new Scanner(System.in);
        Database.search(con);
        System.out.print("请输入被删除数据的ID：");
        int id = sc.nextInt();
        String sql = "delete from q1 where id = ?";
        try {
            PreparedStatement stmt = con.prepareStatement(sql);
            stmt.setInt(1, id);
            stmt.executeUpdate();
            System.out.println("删除成功！");
        } catch (SQLException e) {
            System.out.println("删除失败！");
            e.printStackTrace();
        }

    }

    //查找数据
    public static void search(Connection con) {
        Scanner sc = new Scanner(System.in);
        System.out.print("请输入关键词：");
        String keyWord = sc.next();
        String sql = "select * from q1 where id like ? or 姓名 like ? or 性别 like ? or 年龄 like ?";
        try {
            PreparedStatement stmt = con.prepareStatement(sql);
            stmt.setString(1, "%" + keyWord + "%");
            stmt.setString(2, "%" + keyWord + "%");
            stmt.setString(3, "%" + keyWord + "%");
            stmt.setString(4, "%" + keyWord + "%");
            ResultSet rs = stmt.executeQuery();
            System.out.printf("%-10s", "ID");
            System.out.printf("%-10s", "姓名");
            System.out.printf("%-10s", "性别");
            System.out.println("年龄");
            while (rs.next()) {
                System.out.printf("%-10s", rs.getString(1));
                System.out.printf("%-10s", rs.getString(2));
                System.out.printf("%-10s", rs.getString(3));
                System.out.println(rs.getString(4));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

    }

    //修改数据
    public void edit(Connection con) {
        Scanner sc = new Scanner(System.in);
        Database.search(con);
        System.out.print("请输入被修改数据的ID：");
        int id = sc.nextInt();
        System.out.print("请输入新的年龄：");
        int age = sc.nextInt();
        String sql = "update q1 set 年龄 = ? where id = ?";
        try {
            PreparedStatement stmt = con.prepareStatement(sql);
            stmt.setInt(1, age);
            stmt.setInt(2, id);
            stmt.executeUpdate();
            System.out.println("修改成功！");
        } catch (SQLException e) {
            System.out.println("修改失败！");
            e.printStackTrace();
        }
    }

    public void end(Connection con){
        try {
            con.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}


public class Main {
    public static void main(String[] args) {
        Database one = new Database();
        Connection con = one.connection();
        while (true) {
            System.out.println("请选择功能：");
            System.out.println("1、增加数据");
            System.out.println("2、删除数据");
            System.out.println("3、查找数据");
            System.out.println("4、修改数据");
            System.out.println("0、退出程序");
            Scanner sc = new Scanner(System.in);
            String c = sc.next();
            switch (c) {
                case "1":
                    one.add(con);
                    break;
                case "2":
                    one.delete(con);
                    break;
                case "3":
                    one.search(con);
                    break;
                case "4":
                    one.edit(con);
                    break;
                case "0":
                    one.end(con);
                    System.exit(0);
                    break;
            }
        }
    }
}
